﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Controls.Primitives;
using System.Windows.Controls;
using System.Windows.Documents;
using static System.Net.Mime.MediaTypeNames;

namespace WpfApp2
{
    public class SqlHelper
    {
        //连接字符串
        private static readonly string connStr = "Data Source = .; Initial Catalog = Test; User Id = sa; PassWord = 123456;";
        
        /// <summary>
        /// 从数据中读取数据方法
        /// </summary>
        /// <param name="filter">搜索条件</param>
        /// <returns></returns>
        public static List<Students> ExecuteReader(string filter = "1=1")
        {
            try
            {
                List<Students> students = new List<Students>();
                string sql = "select * from Students";
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    SqlCommand command = new SqlCommand();
                    command.Connection = conn;
                    command.CommandText = $@"select * from Students where {filter}";

                    //打开连接
                    conn.Open();
                    var dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        Students students1 = new Students();
                        students1.Id = int.Parse(dr["Id"].ToString());
                        students1.Name = dr["Name"].ToString();
                        students1.PassWord = dr["PassWord"].ToString();
                        students.Add(students1);
                    }
                    dr.Close();
                    conn.Close();
                }
                return students;
            }
            catch
            {
                throw;
            }
        }


        public static async Task<(int, int, List<Students>)> GetDataWithCountAsync(int pageNumber, int pageSize, string filter = "1=1")
        {
            int total = 0;
            List<Students> list = new List<Students>();
            try
            {
                SqlDataReader dataReader = null;
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    // 获取总数
                    using (var countCommand = conn.CreateCommand())
                    {
                        countCommand.CommandText = $@"SELECT COUNT(*) FROM Students where {filter}";
                        total = Convert.ToInt32(countCommand.ExecuteScalar());
                    }
                    //获取分页数据
                    using (var dataCommand = conn.CreateCommand())
                    {

                        //通常是因为 SQL Server 不支持 LIMIT 语法3。SQL Server 使用 TOP 或者结合 OFFSET 和 FETCH NEXT 来实现类似的功能。
                        //dataCommand.CommandText = $@"Select * From Students where {filter}  ORDER BY Id LIMIT @PageSize OFFSET @Offset";

                        dataCommand.CommandText = $@"Select * From Students where {filter} ORDER BY Id OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;";

                        dataCommand.Parameters.AddWithValue("@PageSize", pageSize);
                        dataCommand.Parameters.AddWithValue("@Offset", (pageNumber - 1) * pageSize);

                        using (dataReader = dataCommand.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                list.Add(new Students
                                {
                                    Id = int.Parse(dataReader["Id"].ToString()),
                                    Name = dataReader["Name"].ToString(),
                                    PassWord = dataReader["PassWord"].ToString()
                                });
                            }
                            dataReader.Close();
                        }
                    }
                    conn.Close();
                    return (pageNumber, total, list);
                }
            }
            catch
            {
                throw;
            }
        }


        public static void WriteOperate(Students student)
        {
            //没问题就执行，有问题就抛出
            try
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    //打开链接
                    conn.Open();

                    using (var insertCommand = conn.CreateCommand())
                    {
                        insertCommand.CommandText = $@"INSERT INTO  Students (Name, PassWord) values (@Name, @PassWord)";
                        insertCommand.Parameters.AddWithValue("@Name", student.Name);
                        insertCommand.Parameters.AddWithValue("@PassWord", student.PassWord);
                        var aaa = insertCommand.ExecuteNonQuery();
                    }
                    conn.Close();
                }
            }
            catch
            {
                throw;
            }
        }

        public static void DeleteOperate(string paramater)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    //打开链接
                    conn.Open();
                    using (var DeleteCommand = conn.CreateCommand())
                    {
                        DeleteCommand.CommandText = $@"Delete From Students where {paramater}";
                        var aaa = DeleteCommand.ExecuteNonQuery();
                    }
                    conn.Close();

                }
            }
            catch
            {
                throw;
            }
        }

        public static void UpdateOperate(Students paramater)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    //打开链接
                    conn.Open();
                    using (var UpdateCommand = conn.CreateCommand())
                    {
                        UpdateCommand.CommandText = $@"update Students set Name = @Name, PassWord = @PassWord where Id = @Id";
                        UpdateCommand.Parameters.AddWithValue("@Id", paramater.Id);
                        UpdateCommand.Parameters.AddWithValue("@Name", paramater.Name);
                        UpdateCommand.Parameters.AddWithValue("@PassWord", paramater.PassWord);
                        var count = UpdateCommand.ExecuteNonQuery();
                        var aaa = 123;
                    }
                    conn.Close();
                }
            }
            catch
            {
                throw;
            }
        }


    }



    #region 废弃代码
    /// <summary>
    /// 执行Sql语句或者存储过程，并返回收到影响的行数
    /// </summary>
    /// <param name="sql">要执行的SQL语句</param>
    /// <returns></returns>
    //public static int ExecuteEonQuery(string sql)
    //{
    //    try
    //    {
    //        int count = 0;
    //        using (SqlConnection conn = new SqlConnection(connStr))
    //        {
    //            using (SqlCommand command = new SqlCommand(sql, conn))
    //            {
    //                //打开连接
    //                conn.Open();
    //                count = command.ExecuteNonQuery();
    //                conn.Close();
    //            }
    //        }
    //        return count;
    //    }
    //    catch (Exception ex)
    //    {
    //        Console.WriteLine("数据查询失败！" + ex.Message);
    //    }
    //}
    #endregion


    //private static Boolean TestConnection(string connectionString1)
    //{
    //    using (SqlConnection connection = new SqlConnection())
    //    {
    //        connection.ConnectionString = connectionString1;
    //        connection.Open();
    //        connection.Close();
    //        return true;
    //    }
    //}
}
